package com.projectName.www.dao;

import com.projectName.www.util.JDBCUtils;
import com.projectName.www.po.Order;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OrderDaoImpl implements OrderDao {
    Order order=null;
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    /**
     **合并查询，优化代码
     * @param id,limit
     * @return orderlist
     */

    public List<Order> findOrderBy(String id,String limit){
        List<Order>orderList=new ArrayList<>();
        try {

            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "select * from orders ";//写出sql语句


            if(limit!=null&&id!=null) {
                if (limit == "id") {
                    sql =sql+ " WHERE oId=?";

                } else if (limit == "ctm"){
                    sql =sql + "WHERE ctmid=?";
                }else if(limit=="slr"){
                    sql=sql + "WHERE slrid=?";
                }

                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1,id);
            }else{
                preparedStatement = connection.prepareStatement(sql);
            }

            resultSet = preparedStatement.executeQuery();//获得查询结果集

            while (resultSet.next()){

                order = new Order();

                order.setoId(resultSet.getInt("oId"));//将数据库中取出来的id字段字符类型转换为整型并赋给order对象

                order.setoTime(resultSet.getTimestamp("oTime"));

                order.setoNumber(resultSet.getInt("oNumber"));

                order.setTolPrice(resultSet.getBigDecimal("tolPrice"));

                order.setStatus(resultSet.getString("status"));

                order.setCtmid(resultSet.getInt("ctmid"));

                order.setslrid(resultSet.getInt("slrid"));

                orderList.add(order);
            }

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
        return orderList;
    }


    @Override
    public List<Order> findall() {
        List<Order>orderList=new ArrayList<>();
        orderList=this.findOrderBy(null,null);
        return orderList;
    }

    @Override
    public Order findById(String oid) {

        List<Order>orderList=this.findOrderBy(oid,"id");
        Order order=orderList.get(0);//获取第一位
        return order;
    }

    @Override
    public List<Order> findOrderByCtm(String uid) {
        List<Order>orderList=new ArrayList<>();
        orderList=this.findOrderBy(uid,"ctm");
        return orderList;
    }

    @Override
    public List<Order> findOrderBySlr(String uid) {

        List<Order>orderList=new ArrayList<>();
        orderList=this.findOrderBy(uid,"slr");
        return orderList;
    }

    @Override
    public void update(Order order) {
            try {
                connection = JDBCUtils.getConnection();//首先连接数据库

                //改变姓名，联系方式，地址，简介
                /*
               tolPrice=?,status=?

                 */
                String sql = "update orders set tolPrice=?,status=? where id=?";//写出sql语句

                preparedStatement = connection.prepareStatement(sql);//执行sql语句

                preparedStatement.setBigDecimal(1,order.getTolPrice());//为sql语句中的变量赋值
                preparedStatement.setString(2,order.getStatus());//为sql语句中的变量赋值
                preparedStatement.setInt(3,order.getoId());//为sql语句中的变量赋值
                preparedStatement.executeUpdate();


            } catch (SQLException e) {
                throw new DaoException(e.getMessage(),e);
            } finally {
                JDBCUtils.close(resultSet,preparedStatement,connection);
            }
    }

    @Override
    public void connect(int oid, int did) {

        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "INSERT INTO lineitem (oid,did) VALUES (?,?)";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setInt(1,oid);//为sql语句中的变量赋值
            preparedStatement.setInt(2,did);//为sql语句中的变量赋值

            preparedStatement.executeUpdate();


        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }

    @Override
    public int insert(Order order) {
        int oid=0;//获取订单主键名
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "INSERT INTO orders (oId,oTime,oNumber,tolPrice,status,ctmid,slrid) VALUES (null,?,?,?,?,?,?)";//写出sql语句

            preparedStatement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);//执行sql语句

            preparedStatement.setTimestamp(1,order.getoTime());//为sql语句中的变量赋值

            preparedStatement.setInt(2,order.getoNumber());//为sql语句中的变量赋值

            preparedStatement.setBigDecimal(3,order.getTolPrice());//为sql语句中的变量赋值

            preparedStatement.setString(4,order.getStatus());//为sql语句中的变量赋值

            preparedStatement.setInt(5,order.getCtmid());//为sql语句中的变量赋值

            preparedStatement.setInt(6,order.getslrid());//为sql语句中的变量赋值

            preparedStatement.executeUpdate();

            ResultSet rs = preparedStatement.getGeneratedKeys();

            if(rs.next()){
                oid=rs.getInt(1);
            }
            //sql语句
            /**
             * 流程
             */
        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
        return oid;
    }

    /**
     * 删除功能
     * @param oid
     */
    @Override
    public void delete(String oid) {

        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "DELETE FROM orders WHERE oId=?";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setString(1,oid);//为sql语句中的变量赋值

            preparedStatement.executeUpdate();//操作

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }





}
